﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

using JigsawService.Parsers;

namespace JigsawService.DataAccess.Security
{
    public class DataGroupDA : JigsawBase
    {
        APIMessage _apimessage;
        string _dbstr;

        public DataGroupDA(APIMessage apiMessage)
        {
            _apimessage = apiMessage;
            _dbstr = ConfigurationManager.AppSettings["SecurityDB"].ToString();
        }


        // ********************************************************************
        // Select section *****************************************************
        // ********************************************************************
        # region Select Section (returns datatable)


        /// <summary>
        /// returns a list of all active datagroups
        /// </summary>
        public void GetAllGroups()
        {
            _apimessage.StateMessage = "DataGroupDA GetAllGroups: ";
            SqlCommand scmd = new SqlCommand();

            scmd.CommandText = "Select *" +
                                " From DataGroup" +
                                " Where RowState = 'Active'";

            SelectBase(scmd, _dbstr, _apimessage);
        }

        /// <summary>
        /// 
        /// </summary>
        public void GetGroupVersions(string groupID)
        {
            _apimessage.StateMessage = "DataGroupDA GetGroupVersions: ";
            SqlCommand scmd = new SqlCommand();

            scmd.CommandText = "Select *" +
                                " From DataGroup" +
                                " Where GlobalID = @GlobalID" +
                                " Order By RowState ASC, GlobalDate DESC";

            // assign parameters and param values to command object (if any)
            scmd.Parameters.Add("@GlobalID", SqlDbType.VarChar, 50).Value = groupID;

            SelectBase(scmd, _dbstr, _apimessage);
        }

        # endregion


        // ********************************************************************
        // Action query section ***********************************************
        // ********************************************************************
        # region Action Section (returns rows affected)

        /// <summary>
        /// Common action query to execute insert, update or delete as transaction
        /// (customized dml and parameters per database table - calls ActionBase method of JigsawBase class)
        /// </summary>
        public void DataGroupAction(string actiontype, string globalid, string groupname, string description)
        {
            _apimessage.StateMessage = "MethodsDA MethodsAction: ";
            SqlCommand sqlCmd = new SqlCommand();

            // assign input parameters as values to the dml parameters
            sqlCmd.Parameters.Add("@GlobalID", SqlDbType.VarChar, 50).Value = globalid;
            sqlCmd.Parameters.Add("@Location", SqlDbType.VarChar, 50).Value = _apimessage.Location;
            sqlCmd.Parameters.Add("@Description", SqlDbType.VarChar, 100).Value = description;
            sqlCmd.Parameters.Add("@GroupName", SqlDbType.VarChar, 50).Value = groupname;
            sqlCmd.Parameters.Add("@ModID", SqlDbType.VarChar, 50).Value = _apimessage.UserID;

            string errMsg = "";
            switch (actiontype.ToLower())
            {
                case "insert":
                    // tests for duplicate rolename and duplicate record, then inserts new record
                    sqlCmd.CommandText = "DECLARE @dupgroup INT" +
                                        " SET @dupgroup = (SELECT count(*) FROM DataGroup WHERE GroupName = @GroupName AND RowState = 'Active');" +
                                        " DECLARE @duprow INT" +
                                        " SET @duprow = (SELECT count(*) FROM DataGroup WHERE GlobalID = @GlobalID AND RowState = 'Active');" +
                                        " IF (@dupgroup = 0 AND @duprow = 0)" +
                                        " Insert DataGroup (GlobalID, Location, GroupName, Description, ModID)" +
                                        " Values (@GlobalID, @Location, @GroupName, @Description, @ModID)";
                    // set error message for insert
                    errMsg = "Duplicate datagroup record found - new record was not created.";
                    break;

                case "update":
                    // updates old record, then inserts new version - also sets any existing duplicates to edited state
                    sqlCmd.CommandText = "UPDATE DataGroup SET RowState = 'Edited' WHERE GlobalID = @GlobalID AND RowState = 'Active';" +
                                        " IF (@@ROWCOUNT > 0)" +
                                        " Insert DataGroup (GlobalID, Location, GroupName, Description, ModID)" +
                                        " Values (@GlobalID, @Location, @GroupName, @Description, @ModID)";
                    // set error message for update
                    errMsg = "Unable to update old record - new version was not created.";
                    break;

                case "delete":
                    // updates old record, then inserts new version with deleted state - also sets any existing duplicates to edited state
                    sqlCmd.CommandText = "UPDATE DataGroup SET RowState = 'Edited' WHERE GlobalID = @GlobalID AND RowState = 'Active';" +
                                        " IF (@@ROWCOUNT > 0)" +
                                        " Insert DataGroup (GlobalID, Location, GroupName, Description, RowState, ModID)" +
                                        " Values (@GlobalID, @Location, @GroupName, @Description, 'Deleted', @ModID)";
                    // set error message for delete
                    errMsg = "Unable to update old record - new version was not created.";
                    break;
            }

            // execute action query
            ActionBase(actiontype.ToLower(), sqlCmd, _dbstr, errMsg, _apimessage, globalid);
        }

        # endregion
    }
}